通过外表导入至湖仓版

AnalyticDB for MySQL企业版、基础版及湖仓版支持通过外表读取并导入外部数据。导入数据时,您可以选择常规导入和弹性导入两种方式。弹性导入相较于常规导入,可以大幅减少资源的消耗,降低导入过程中对在线读写业务的影响。本文介绍如何通过外表查询OSS数据,并将OSS的数据导入AnalyticDB for MySQL集群。

前提条件

  • AnalyticDB for MySQL集群的产品系列为企业版、基础版或湖仓版

  • AnalyticDB for MySQL集群与OSS存储空间位于相同地域。

  • 已将数据文件上传至OSS目录中。具体操作,请参见上传文件

示例数据说明

本文示例将数据文件person上传至OSS中的testBucketName/adb/dt=2023-06-15目录,数据行分隔符为换行符,列分隔符为英文逗号(,)。person中的示例数据如下:

1,james,10,2023-06-15
2,bond,20,2023-06-15
3,jack,30,2023-06-15
4,lucy,40,2023-06-15       

操作步骤

  1. 进入SQL开发编辑器。

    1. 登录云原生数据仓库AnalyticDB MySQL控制台,在左上角选择集群所在地域。在左侧导航栏,单击集群列表,在企业版、基础版或湖仓版页签下,单击目标集群ID。

    2. 在左侧导航栏,单击作业开发 > SQL开发

  2. 导入数据。

    数据导入方式分为常规导入(默认)和弹性导入。常规导入在计算节点中读取源数据,然后在存储节点中构建索引,消耗计算资源和存储资源。弹性导入在Serverless Spark Job中读取源数据和构建索引,消耗Job型资源组的资源。仅内核版本3.1.10.0及以上且已创建Job型资源组的企业版、基础版及湖仓版集群支持弹性导入数据。更多内容,请参见数据导入方式介绍

    常规导入

    1. 创建外部数据库。

      CREATE EXTERNAL DATABASE adb_external_db;
    2. 创建外表。使用CREATE EXTERNAL TABLE语句在外部数据库adb_external_db中创建OSS外表。本文以adb_external_db.person为例。

      说明

      AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。

      创建OSS非分区外表

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      创建OSS分区外表

      创建OSS分区外表,并添加分区,才能查询出OSS分区外表的数据。

      1. 创建OSS分区外表

        CREATE EXTERNAL TABLE adb_external_db.person
        (
         id INT,
         name VARCHAR(1023) ,
         age INT
        )
        PARTITIONED BY (dt STRING)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
        STORED AS TEXTFILE
        LOCATION  'oss://testBucketName/adb/';
      2. 添加分区。您可以通过ALTER TABLE ADD PARTITION手动添加分区,也可以通过MSCK REPAIR TABLE自动识别并添加分区。

        ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';
        说明

      OSS外表的语法说明,请参见CREATE EXTERNAL TABLE

    3. 查询数据。

      数据表创建成功后,您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。

      SELECT * FROM adb_external_db.person;

      返回结果如下:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)
    4. AnalyticDB for MySQL中创建数据库。如果有已创建的数据库,可以忽略本步骤。示例如下:

      CREATE DATABASE adb_demo; 
    5. AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下:

      说明

      创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTE BY HASH(id);
    6. 向表中导入数据。

      • 方法一:使用INSERT INTO语句导入数据,当主键重复时会自动忽略当前写入数据,数据不做更新,作用等同于INSERT IGNORE INTO,更多信息,请参见INSERT INTO。示例如下:

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
      • 方法二:使用INSERT OVERWRITE INTO语句同步导入数据,会覆盖表中原有的数据。示例如下:

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
      • 方法三:使用INSERT OVERWRITE INTO语句异步导入数据,更多信息,请参见异步写入。示例如下:

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

    弹性导入

    1. 创建数据库。如果有已创建的数据库,可以忽略本步骤。示例如下:

      CREATE DATABASE adb_demo; 
    2. 创建外表。

      说明
      • AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。

      • 弹性导入仅支持CREATE TABLE语句创建外表。

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://<bucket-name>/adb/oss_import_test_data.csv",
          "accessid":"LTAI5t8sqJn5GhpBVtN8****",
          "accesskey":"HlClegbiV5mJjBYBJHEZQOnRF7****",
          "delimiter":","
      }';
      重要

      创建外表时,CSV、Parquet、ORC格式的外表支持设置的TABLE_PROPERTIES参数不同:

      • CSV格式:仅支持设置endpointurlaccessidaccesskeyformatdelimiternull_valuepartition_column参数。

      • Parquet格式:仅支持设置endpointurlaccessidaccesskeyformatpartition_column参数。

      • ORC格式:仅支持设置endpointurlaccessidaccesskeyformatpartition_column参数。

      外表支持设置的参数及参数说明,请参见OSS非分区外表OSS分区外表

    3. 查询数据。

      数据表创建成功后,您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。

      SELECT * FROM oss_import_test_external_table;

      返回结果如下:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)
    4. AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下:

      说明

      创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);
    5. 导入数据。

      重要

      弹性导入仅支持通过INSERT OVERWRITE INTO语句导入数据。

      • 方法一:执行INSERT OVERWRITE INTO弹性导入数据,会覆盖表中原有的数据。示例如下:

        /+*elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;
      • 方法二:异步执行INSERT OVERWRITE INTO弹性导入数据。通常使用SUBMIT JOB提交异步任务,由后台调度。

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;
        重要

        异步提交弹性导入任务时,不支持设置优先级队列。

        返回结果如下:

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 2023081517195102101701907203151****** |

      使用SUBMIT JOB提交异步任务后,返回结果仅表示异步任务提交成功。您可以通过job_id终止异步任务或查询异步任务状态,判断任务是否执行成功。具体操作,请参见异步提交导入任务

      Hint参数说明:

      • elastic_load:是否使用弹性导入方式。取值:truefalse(默认值)。

      • elastic_load_configs:弹性导入方式支持配置的参数。参数需使用方括号([ ])括起来,且多个参数之间以竖线(|)分隔,支持配置的参数如下表所示:

        参数

        是否必填

        说明

        adb.load.resource.group.name

        执行弹性导入任务的Job资源组名称。

        adb.load.job.max.acu

        单个弹性导入任务最多使用的资源。单位为ACU,最小值为5 ACU。默认值为集群Shard个数+1。

        执行如下语句可查询集群Shard个数:

        SELECT count(1) FROM information_schema.kepler_meta_shards;

        spark.driver.resourceSpec

        Spark driver的资源规格。默认值为small。取值范围,请参见Spark资源规格列表的型号列。

        spark.executor.resourceSpec

        Spark executor的资源规格。默认值为large。取值范围,请参见Spark资源规格列表的型号列。

        spark.adb.executorDiskSize

        Spark executor的磁盘容量,取值范围为(0,100],单位为GiB,默认值为10 Gi。更多信息,请参见指定Driver和Executor资源

    6. (可选)查看已提交的导入任务是否为弹性导入任务。

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      返回结果如下:

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 2023081517195102101701907203151****** |       1          |
      +---------------------------------------+------------------+

      is_elastic_load的返回值为1,表示已提交的导入任务是弹性导入任务;若为0,则表示已提交的导入任务是常规导入任务。